<?php
namespace Api\Model\Amazon;
use Think\Exception;
class ReportUnsuppressedInventoryModel extends CommonModel{
    // 数据表前缀
    protected $tablePrefix   = 'api_';
    // 数据库配置
    protected $connection    = 'fbawarehouse';
    // 数据表名（不包含表前缀）
    protected $tableName     = 'report_unsuppressed_inventory';

    protected $trueTableName = 'api_report_unsuppressed_inventory';

    /**
     * 描述：从api_report_unsuppressed_inventory抓取数据到api_asins表中
     */
    public function selectInsertToAccountSellerSku(){
        $accountSellerSkuData = $this->field(array('account_id','sku','fnsku','asin','product_name'))
                          ->where("`product_name` != ''")
                          ->select();

        $skuData = array();
        foreach ($accountSellerSkuData as $data){
            $siteId = M('amazonorder_account_troop_site',' ','DB_fbawarehouse')
                ->field('site_id')
                ->where('account_id = ' . $data['account_id'])
                ->find();

            $tempData['account_id'] = $data['account_id'];
            $tempData['site_id']    = $siteId['site_id'];
            $tempData['seller_sku'] = $data['sku'];
            $tempData['fnsku']      = $data['fnsku'];
            $tempData['asin']       = $data['asin'];
            $tempData['title']      = $data['product_name'];


            $skuData[] = $tempData;
        }
        unset($accountSellerSkuData);
        return $skuData;
    }

    /**
     * @param $rowLists
     * @param $tableName
     * 描述：插入解析数据
     */
    public function insertData($tableName,$rowLists){

        /*$sql = 'SELECT `create_time` FROM `' . $tableName . '` ORDER BY `id` DESC LIMIT 1';
        $maxCreateTime = $this->query($sql);
        $maxCreateTime = !empty($maxCreateTime) ? $maxCreateTime[0]['create_time'] : '';
        if(!empty($maxCreateTime) && ((time() - strtotime($maxCreateTime)) > 86400 )) {
            $sql = 'TRUNCATE TABLE  `' . $tableName . '`';
            $this->query($sql);
        }*/

        /*if(!empty($rowLists)) {
            $rowListsChunks = array_chunk($rowLists, 500);
            foreach($rowListsChunks as $rowListsChunk) {
                $this->addAll($rowListsChunk);
            }
        }*/
        if(!empty($rowLists)) {
            foreach($rowLists as $row) {
                try {
                    $this->add($row);
                } catch(Exception $ex) {
                    continue;
                }
            }
        }


    }

    /**
     * @param array $options
     * @return bool|mixed
     * 描述：根据条件查询数据
     */
    public function selectData($options = array()){
        $data = $this->where($options)->select();

        return isset($data) ? $data : false;
    }

    /**
     * @param $report_file_id
     * 描述：计算在途库存
     */
    public function shippingCalculate ($reportFileId){
        if(empty($reportFileId))return false;

        $sql = 'SELECT `rui`.`account_id`,`skus` . `private_sku`,
                SUM(`rui`.`afn_inbound_shipped_quantity` + `rui`.`afn_inbound_working_quantity` 
                +`rui`.`afn_inbound_receiving_quantity`) AS `shipping`
                FROM `api_report_unsuppressed_inventory` AS `rui`
                LEFT JOIN `api_account_seller_sku` AS `skus` 
                ON `skus`.`seller_sku` = `rui`.`sku` AND `rui`.`account_id` = `skus`.`account_id`
                WHERE `rui`.`account_id` != 0 
                AND `rui` . `report_file_id` = '. $reportFileId . ' ';

        $shippingLists = $this->query($sql .
            ' GROUP BY `rui`.`account_id`, `skus`.`private_sku`');

        if(!empty($shippingLists)){
            D('Api/Amazon/ReportStockSale')->insertShippingData($shippingLists);
        }

    }

    public function getUnsuppressedInventoryBySellerSku($options) {
        /*$unsuppressedInventory = array();
        $result  = $this->field('`account_id`,`sku`,(`afn_inbound_working_quantity`
            +`afn_inbound_shipped_quantity`
            +`afn_inbound_receiving_quantity`) AS unsuppressedInventory')
            ->where(array(
                'sku' => array('IN',$skus),
                'create_time' => array('LIKE' ,'%' . date('Y-m-d') . '%')
                ))
            ->order('create_time desc')
            ->select();
        foreach ($result as $value) {
            if (!isset($unsuppressedInventory[$value['account_id'] . ':' .$value['sku']])) {
                $unsuppressedInventory[$value['account_id'] . ':' .$value['sku']] = $value['unsuppressedInventory'];
            }
        }

        return !empty($unsuppressedInventory) ? $unsuppressedInventory : array();*/
        $sql = 'SELECT `arui`.`account_id`,`as`.`shorthand_code`,`aa`.`name` AS accountName,`arui`.`sku`,`arui`.`asin`,
                `aass`.`private_sku`,`sst`.`item`,`aass`.`sale_status_id`,(`arui`.`afn_inbound_working_quantity` +
                `arui`.`afn_inbound_shipped_quantity` + `arui`.`afn_inbound_receiving_quantity`) AS `unsuppressedInventory`
                FROM `api_report_unsuppressed_inventory` AS `arui`
                LEFT JOIN `amazonorder_accounts` AS `aa` ON `aa`.`id` = `arui`.`account_id`
                LEFT JOIN `api_account_seller_sku` AS `aass` ON `aass`.`account_id` = `arui`.`account_id` AND `aass`.`seller_sku` = `arui`.`sku`
                LEFT JOIN `amazonorder_sites` AS `as` ON `aass`.`site_id` = `as`.`id`
                LEFT JOIN `skusystem_sku_taxrate` AS `sst` ON `sst`.`sku` = `aass`.`private_sku`
                WHERE `arui`.`account_id` != 0
                AND `aass`.`private_sku` IN (' . $options['sku'] . ')
                AND `arui`.`create_time` LIKE \'%' . date('Y-m-d') . '%\'
                ORDER BY `arui`.`create_time` DESC';
        echo $sql;exit;
        $result = $this->query($sql);

        $unsuppressedInventory = array();

        foreach ($result as $key => $value) {
            if (!isset($unsuppressedInventory[$value['account_id'] . ':' .$value['sku']])) {
                $unsuppressedInventory[$value['account_id'] . ':' .$value['sku']] = $result[$key];
            }
        }

        return !empty($unsuppressedInventory) ? $unsuppressedInventory : array();
    }

    public function getSingleData() {
        $create_time = $this->field('LEFT(`create_time`,13) AS create_time')
            ->order('`create_time` DESC')
            ->limit(1)
            ->find();

        $sql = 'SELECT `ari`.`sku`, `as`.`shorthand_code`, `aass`.`private_sku`, `aa`.`name`, `ari`.`asin`, 
                (`ari`.`afn_inbound_working_quantity` + `ari`.`afn_inbound_shipped_quantity` + `ari`.`afn_inbound_receiving_quantity`) AS "onway" 
                FROM `api_report_unsuppressed_inventory` AS `ari` 
                LEFT JOIN `amazonorder_accounts` AS `aa` 
                ON `ari`.`account_id` = `aa`.`id` 
                LEFT JOIN `api_account_seller_sku` AS `aass` 
                ON `ari`.`account_id` = `aass`.`account_id` AND `ari`.`sku` = `aass`.`seller_sku` 
                LEFT JOIN `amazonorder_sites` AS `as` ON `as`.`id` = `aass`.`site_id` 
                WHERE `ari`.`create_time` LIKE "' .$create_time['create_time'] .'%"';

        $data = $this->query($sql);

        $title = array(
            'sku' => 'SellerSKU',
            'shorthand_code' => '站点',
            'private_sku' => '公司SKU',
            'name' => '账号名',
            'asin' => 'ASIN',
            'onway' => '在途库存'
        );
        $result['data'] = !empty($data) ? $data : array();
        $result['title'] = $title;
        $result['fileName'] = "实时国际在途库存报表";
        return $result;
    }
}